import pandas as pd
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from models import *  # 确保从models.py导入正确的模型
import os
import random
from datetime import datetime, timedelta
from models import db, Product, MaintainRecord
from models import Product, InspectionRecord

def import_org_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 读取Excel文件
        file_path = os.path.abspath('xls/assets.xlsx')
        df = pd.read_excel(file_path, sheet_name='orgs', header=0)
        # print(df)
        # 维修状态映射字典
        
        new_records = []
        
        for index, row in df.iterrows():
        
           
            
            new_records.append(Organization(
                
                
               
                org_name = row['org_name'],
                org_code = row['org_code'],
                org_level = row['org_level'],
            	is_active = row['is_active'],
                parent_id = row['parent_id'],
                address = row['address'],
                contact_person = row['contact_person'],
                contact_phone = row['contact_phone'],
                short_name = row['short_name'],
                short_name_c = row['short_name_c'],


                
              
            ))

        # 批量插入数据
        session.bulk_save_objects(new_records)
        session.commit()
        print(f"成功导入 {len(new_records)} 条机构记录")
        
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()



def import_department_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 读取Excel文件
        file_path = os.path.abspath('xls/assets.xlsx')
        df = pd.read_excel(file_path, sheet_name='departments', header=0)
        # print(df)
        # 维修状态映射字典
        
        new_records = []
        
        for index, row in df.iterrows():
        
           
            
            new_records.append(Department(
                dept_code = row['department_code'],
                org_name = row['org_name'],
                org_id = row['org_id'],
                dept_name = row['department_name'],
                # is_active = row['is_active'],
                # department_level = row['department_level'],
                # parent = row['parent'],
                # address = row['address'],
                # contact_person = row['contact_person'],
                # contact_phone = row['contact_phone'],

            ))

        # 批量插入数据
        session.bulk_save_objects(new_records)
        session.commit()
        print(f"成功导入 {len(new_records)} 条部门记录")
        
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()




def import_product_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 读取Excel文件
        file_path = os.path.abspath('xls/assets.xlsx')
        df = pd.read_excel(file_path, sheet_name='products', header=0)
        # df = df.head(218)
        # print(df)
        # 维修状态映射字典
        
        new_records = []
        
        for index, row in df.iterrows():
            try:
           
            
                new_records.append(Product(
                    # 修复字段映射，移除repair_id字段
                    # asset_id = str(row['asset_id']) if pd.notna(row['asset_id']) else '',  # 转为字符串
                    product_name = row['asset_name'],
                    product_code = str(row['asset_code']) if pd.notna(row['asset_code']) else '',
                    
                    
                    equipment_category = str(row['category']) if pd.notna(row['category']) else '',
                    price = pd.to_numeric(row['price']) if pd.notna(row['price']) else 0.0,
                    purchase_date = pd.to_datetime(row['purchase_date']).date() if pd.notna(row['purchase_date']) else None,
                    # annual_maintenance_plan = int(row['annual_maintenance_plan']) if pd.notna(row['annual_maintenance_plan']) else 0,
                    # last_inspection_date = pd.to_datetime(row['last_inspection_date']).date() if pd.notna(row['last_inspection_date']) else None,
                    # last_repair_date = pd.to_datetime(row['last_repair_date']).date() if pd.notna(row['last_repair_date']) else None,
                    # last_maintenance_date = pd.to_datetime(row['last_maintenance_date']).date() if pd.notna(row['last_maintenance_date']) else None,
                    manufacturer = row['brand'],
                    # standard = str(row['standard']) if pd.notna(row['standard']) else '',  # 处理字符串字段
                    service_life = int(float(row['service_life'])) if pd.notna(row['service_life']) else 0,
                    running_status = str(row['status']) if pd.notna(row['status']) else '',
                    # is_active = bool(pd.to_numeric(row['is_active'], errors='coerce') == 1) if pd.notna(row['is_active']) else False,  # 强化类型转换
                    org_name = str(row['org_name']) if pd.notna(row['org_name']) else '',
                    org_short_name_c = str(row['org_short_name_c']) if pd.notna(row['org_short_name_c']) else '',
                    department_name = row['department_name'],
                    # location = None,
                    # maintenance_level = None,
                
                    # account_holder_name = None,
                    # account_holder_code = None,
                    # user_name = None,
                    # user_code = None,
                    # manufacturer = None,
                    
                    # commissioning_date = None,
                    # supplier_code = None,
                    # supplier_name = None,
                    # is_enabled = None,
                    # annual_inspection_frequency = None,
                    
                    # inspection_plan_name = None,
                
                    # submitter = None,
                    # submit_time = None,
                    # update_time = None,
                    
              
                    # location = row['location'],
                    # maintenance_level = row['maintenance_level'],
                
                    # account_holder_name = row['account_holder_name'],
                    # account_holder_code = row['account_holder_code'],
                    # user_name = row['user_name'],
                    # user_code = row['user_code'],
                    # manufacturer = row['manufacturer'],
                    
                    # commissioning_date = pd.to_datetime(row['commissioning_date']).date() if pd.notna(row['commissioning_date']) else None,
                    # supplier_code = row['supplier_code'],
                    # supplier_name = row['supplier_name'],
                    # is_enabled = row['is_enabled'],
                    # annual_inspection_frequency = row['annual_inspection_frequency'],
                    
                    # inspection_plan_name = row['inspection_plan_name'],
                
                    # submitter = row['submitter'],
                    # submit_time = pd.to_datetime(row['submit_time']).date() if pd.notna(row['submit_time']) else None,
                    # update_time = pd.to_datetime(row['update_time']).date() if pd.notna(row['update_time']) else None,
                    
              
              
                ))
            except Exception as e:
                print(f"第{index+2}行数据错误（Excel行号）：")
                print(f"错误字段：is_active 值：{row['is_active']}")
                print(f"错误详情：{str(e)}")
                raise    

        # 批量插入数据
        session.bulk_save_objects(new_records)
        session.commit()
        print(f"成功导入 {len(new_records)} 条资产记录")
        
    except Exception as e:
        session.rollback()
        # session.commit()

        print(f"导入失败:{str(e)} ")
    finally:
        session.close()


# def import_repair_data():
#     db_path = os.path.abspath('instance/mis.db')
#     engine = create_engine(f'sqlite:///{db_path}')
#     Session = sessionmaker(bind=engine)
#     session = Session()
#     try:
#         # 读取Excel文件
#         file_path = os.path.abspath('xls/repairelist.xlsx')
#         df = pd.read_excel(file_path, sheet_name='sheet1', header=0)
#         # print(df)
#         # 维修状态映射字典
        
#         new_records = []
        
#         for index, row in df.iterrows():
        
#             try:
#                 repair_duration = float(row['repair_duration']) if pd.notna(row['repair_duration']) else 0
#             except ValueError:
#                 repair_duration = 0

            
#             new_records.append(RepairRecord(
                
                
               
#                 report_date = pd.to_datetime(row['报修时间']).date() if pd.notna(row['报修时间']) else None,
#                 org_name = row['医院名称'],
#                 org_shortname_c = row['org_shortname_c'],
#                 department_name = row['所属科室'],
#                 product_name = row['设备名称'],
#                 product_code = row['资产编号'],
#                 product_category = row['通用类型'],
#                 product_brand = row['品牌'],
#                 product_model = row['型号'],
#                 fault_description = row['故障描述'],
#                 repair_status = row['维修状态'],
#                 repair_content = row['维修内容'],
                


#                 repair_duration = round(repair_duration,2)
                
                

#             ))

#         # 批量插入数据
#         session.bulk_save_objects(new_records)
#         session.commit()
#         print(f"成功导入 {len(new_records)} 条维修记录")
        
#     except Exception as e:
#         session.rollback()
#         print(f"导入失败:{str(e)} ")
#     finally:
#         session.close()

def import_repair_data2():
    import random
    from datetime import datetime, timedelta

    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        products = session.query(Product).all()
        if not products:
            print("无可用设备数据")
            return

        now = datetime.now()
        start_time = datetime(2025, 1, 1, 0, 0, 0)

        repair_callers = ['张三', '李四', '王五', '赵六', '钱七', '孙八']
        repair_types = ['日常维修', '紧急维修', '预防性维修', '故障维修']
        fault_descriptions = ['无法开机', '噪音异常', '显示故障', '电源故障', '按钮失灵', '温度异常']
        repair_results = ['修复完成', '更换部件', '无法修复', '待观察']
        repair_contents = ['更换主板', '清理灰尘', '调整参数', '更换电源', '更换显示屏']
        repair_persons = ['工程师A', '工程师B', '工程师C', '工程师D']
        repair_companies = ['本院', '厂家', '第三方公司']
        replaced_parts = ['主板', '电源', '显示屏', '按钮', '无']
        remarks_list = ['处理及时', '用户满意', '建议加强维护', '多次故障', '首次报修']

        status_choices = (
            ['待响应'] * 1 +
            ['维修中'] * 2 +
            ['已完成'] * 17
        )  # 控制比例，可根据实际需求调整

        new_records = []
        for _ in range(500):
            product = random.choice(products)
            repair_status = random.choice(status_choices)

            # 公共字段
            base_kwargs = dict(
                product_id=product.id,
                product_name=product.product_name,
                product_code=product.product_code,
                product_category=getattr(product, 'equipment_category', ''),
                product_brand=getattr(product, 'manufacturer', ''),
                product_model=getattr(product, 'model', ''),
                org_name=getattr(product, 'org_name', ''),
                org_shortname_c=getattr(product, 'org_short_name_c', ''),
                department_name=getattr(product, 'department_name', ''),
                repair_status=repair_status,
                is_active=True
            )

            # 生成时间
            report_date = start_time + timedelta(seconds=random.randint(0, int((now - start_time).total_seconds())))
            answer_delta = timedelta(hours=random.uniform(1, 24))
            repair_answer_date = report_date + answer_delta
            expected_delta = timedelta(days=random.uniform(1, 5))
            expected_completion_date = (repair_answer_date + expected_delta).date()
            complete_delta = timedelta(days=random.uniform(0, 2), hours=random.uniform(0, 23), minutes=random.uniform(0, 59))
            actual_completion_date = datetime.combine(expected_completion_date, datetime.min.time()) + complete_delta

            repair_answer_duration = round((repair_answer_date - report_date).total_seconds() / 3600, 2)
            repair_duration = round((actual_completion_date - report_date).total_seconds() / 3600, 2)
            repair_cost = round(random.uniform(100, 2000), 2)

            # 根据状态填充字段
            if repair_status == '待响应':
                record = RepairRecord(
                    **base_kwargs,
                    repair_caller=random.choice(repair_callers),
                    report_date=report_date,
                    fault_description=random.choice(fault_descriptions),
                    create_time=report_date,
                    update_time=report_date
                )
                # 状态为待响应，设备设为运行中
                product.running_status = 1
            elif repair_status == '维修中':
                record = RepairRecord(
                    **base_kwargs,
                    repair_caller=random.choice(repair_callers),
                    report_date=report_date,
                    fault_description=random.choice(fault_descriptions),
                    repair_answer_date=repair_answer_date,
                    repair_person=random.choice(repair_persons),
                    repair_company=random.choice(repair_companies),
                    expected_completion_date=expected_completion_date,
                    remarks=random.choice(remarks_list),
                    repair_answer_duration=repair_answer_duration,
                    create_time=report_date,
                    update_time=repair_answer_date
                )
                # 状态为维修中，设备设为运行中
                product.running_status = 1
            else:  # 已完成 或 已取消
                record = RepairRecord(
                    **base_kwargs,
                    repair_caller=random.choice(repair_callers),
                    report_date=report_date,
                    fault_description=random.choice(fault_descriptions),
                    repair_answer_date=repair_answer_date,
                    repair_person=random.choice(repair_persons),
                    repair_company=random.choice(repair_companies),
                    expected_completion_date=expected_completion_date,
                    remarks=random.choice(remarks_list),
                    repair_answer_duration=repair_answer_duration,
                    repair_type=random.choice(repair_types),
                    repair_result=random.choice(repair_results),
                    repair_content=random.choice(repair_contents),
                    repair_cost=repair_cost,
                    repair_duration=repair_duration,
                    actual_completion_date=actual_completion_date,
                    parts_replaced=random.choice(replaced_parts),
                    proof=None,
                    create_time=report_date,
                    update_time=actual_completion_date
                )
                # 状态为已完成或已取消，设备设为非运行
                product.running_status = 0
            new_records.append(record)

        session.bulk_save_objects(new_records)
        session.commit()
        print(f"成功导入 {len(new_records)} 条维修记录")
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()

def import_maintain_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        # 查询所有有效设备
        products = session.query(Product).filter_by(is_active=True).all()
        if not products:
            print("无可用设备数据")
            return

        records = []
        for _ in range(1000):
            product = random.choice(products)
            # 随机生成计划保养日期（2024-01-01 ~ 2024-12-31）
            scheduled_base = datetime(2025, 1, 1)
            scheduled_date = scheduled_base + timedelta(days=random.randint(0, 364))
            completion_status = random.choice(['已完成','已完成','已完成','已完成','已完成','已完成','已完成','已完成','已完成', '未完成'])
            # 判断完成状态，未完成则实际保养日期为空
            if completion_status == '未完成':
                actual_date = None
            else:
                actual_date = scheduled_date + timedelta(days=random.randint(1, 10))

            record = MaintainRecord(
                product_id=product.id,
                product_name=product.product_name,
                product_code=product.product_code,
                org_id=None,  # 如有需要可关联机构ID
                org_name=product.org_short_name_c if hasattr(product, 'org_short_name_c') else product.org_name,
                dept_name=product.department_name if hasattr(product, 'department_name') else "",
                scheduled_date=scheduled_date.date(),
                actual_date=actual_date.date() if actual_date else None,
                maintenance_type=random.choice(['例行保养', '专项保养', '紧急保养']),
                maintenance_content=random.choice(['清洁消毒', '结构检查', '润滑检查', '耗材更换']),
                maintenance_result=random.choice(['合格', '不合格', '待复查']),
                maintenance_person=random.choice(['工人甲', '工人乙', '工人丙']),
                maintenance_company=random.choice(['本公司', '原厂', '第三方']),
                completion_status=completion_status,
                remarks='自动生成数据',
                is_active=True
            )
            records.append(record)

        session.bulk_save_objects(records)
        session.commit()
        print("已成功导入1000条保养记录")
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()




def import_inspection_data():
    db_path = os.path.abspath('instance/mis.db')
    engine = create_engine(f'sqlite:///{db_path}')
    Session = sessionmaker(bind=engine)
    session = Session()
    try:
        products = session.query(Product).filter_by(is_active=True).all()
        if not products:
            print("无可用设备数据")
            return

        records = []
        for _ in range(1000):
            product = random.choice(products)
            # 随机生成计划巡检日期（2025-01-01 ~ 2025-12-31）
            plan_base = datetime(2025, 1, 1)
            scheduled_date = plan_base + timedelta(days=random.randint(0, 364))
            completion_status = random.choice(['已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '已完成', '未完成'])
            # 判断完成状态，未完成则实际巡检日期为空
            if completion_status == '未完成':
                actual_date = None
            else:
                actual_date = scheduled_date + timedelta(days=random.randint(1, 10))

            inspection_type = random.choice(['例行巡检', '专项巡检', '紧急巡检'])
            inspection_content = random.choice(['外观检查', '功能测试', '安全检测', '性能评估'])
            inspection_result = random.choice(['合格', '不合格', '需复查'])
            inspection_person = random.choice(['张三', '李四', '王五', '赵六'])
            inspection_company = random.choice(['本公司', '原厂', '第三方'])
            remarks = '自动生成数据'

            record = InspectionRecord(
                product_id=product.id,
                product_name=product.product_name,
                product_code=product.product_code,
                org_name=product.org_short_name_c if hasattr(product, 'org_short_name_c') else product.org_name,
                dept_name=product.department_name,
                scheduled_date=scheduled_date.date(),
                actual_date=actual_date.date() if actual_date else None,
                inspection_type=inspection_type,
                inspection_content=inspection_content,
                inspection_result=inspection_result,
                inspection_person=inspection_person,
                inspection_company=inspection_company,
                completion_status=completion_status,
                remarks=remarks,
                is_active=True
            )
            records.append(record)

        session.bulk_save_objects(records)
        session.commit()
        print("已成功导入1000条巡检记录")
    except Exception as e:
        session.rollback()
        print(f"导入失败:{str(e)} ")
    finally:
        session.close()




if __name__ == "__main__":
    
   
    # import_org_data()
    # import_department_data()
    # import_product_data()
    # import_repair_data()
    import_repair_data2()
    # import_maintain_data()
    # import_inspection_data()
    pass
    